﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;

namespace STT.Layer.Connection
{
    public class ToolBox:Data
    {
        public ToolBox()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        //Đổ dữ liệu vào DropDownList
        public void LoadDropDownList(DropDownList ddl, string sql, string DisplayMember, string ValueMember)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                ddl.Items.Clear();
                //ddl.SelectedIndex = -1;
                ddl.SelectedValue = null;
                ddl.ClearSelection();     // Clears the selection to avoid the exception (only one of these should be enough but in my application I needed all..)
                //listOrgs.DataSource = new Organization().DTListAll(SiteID);
                ddl.DataSource = GetDataset(sql).Tables[0];
                ddl.DataTextField = DisplayMember;
                ddl.DataValueField = ValueMember;
                ddl.DataBind();
                ddl.Items.Insert(0, new ListItem("------------Select------------", "NA"));
                CloseConnection(cn);
            }
        }
        //
        public void LoadDropDownList2(DropDownList ddl, string sql, string DisplayMember, string ValueMember)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                ddl.Items.Clear();
                //ddl.SelectedIndex = -1;
                ddl.SelectedValue = null;
                ddl.ClearSelection();     // Clears the selection to avoid the exception (only one of these should be enough but in my application I needed all..)
                //listOrgs.DataSource = new Organization().DTListAll(SiteID);
                ddl.DataSource = GetDataset(sql).Tables[0];
                ddl.DataTextField = DisplayMember;
                ddl.DataValueField = ValueMember;
                ddl.DataBind();
                CloseConnection(cn);
            }
        }
        //
        public void LoadDropDownList3(DropDownList ddl, string sql, string id, string DisplayMember, string ValueMember)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                ddl.Items.Clear();
                //ddl.SelectedIndex = -1;
                ddl.SelectedValue = null;
                ddl.ClearSelection();     // Clears the selection to avoid the exception (only one of these should be enough but in my application I needed all..)
                //listOrgs.DataSource = new Organization().DTListAll(SiteID);
                ddl.DataSource = GetDataTableById(sql, id);
                ddl.DataTextField = DisplayMember;
                ddl.DataValueField = ValueMember;
                ddl.DataBind();
                CloseConnection(cn);
            }
        }
        //Đổ dữ liệu vào GridView
        public void LoadGridView(GridView dgv, string sql)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                DataTable dt = GetDataTable(sql);
                dgv.DataSource = dt;
                dgv.DataBind();
                CloseConnection(cn);
            }
        }
        public void LoadGridView2(GridView dgv, string sql)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                DataTable dt = GetDataTable2(sql);
                dgv.DataSource = dt;
                dgv.DataBind();
                CloseConnection(cn);
            }
        }
        //Đổ dữ liệu vào GridView theo Id
        public void LoadGridViewById(GridView dgv, string sql, string id)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                DataTable dt = GetDataTableById(sql, id);
                dgv.DataSource = dt;
                dgv.DataBind();
                CloseConnection(cn);
            }
        }
        //Lấy dữ liệu xuất execl
        public DataTable ExportToExel(DataTable dt2)
        {
            DataTable table = new DataTable();

            // Declare DataColumn and DataRow variables.
            DataColumn column;
            DataRow row;            

            // Create new DataColumn, set DataType, ColumnName and add to DataTable.    
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "MaSV";
            table.Columns.Add(column);

            // Create second column.
            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "TenSV";
            table.Columns.Add(column);
            // Create 3 column.
            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "SDT";
            table.Columns.Add(column);
            // Create 4 column.
            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "Lop";
            table.Columns.Add(column);
            // Create 5 column.
            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "Khoa";
            table.Columns.Add(column);
            // Create 6 column.
            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "Khoas";
            table.Columns.Add(column);
            for (int i = 0; i < dt2.Rows.Count; i++)
            {
                row = table.NewRow();
                row["MaSV"] = dt2.Rows[i]["StudentId"].ToString();
                row["TenSV"] = dt2.Rows[i]["StudentName"].ToString();
                row["SDT"] = dt2.Rows[i]["PhoneNumber"].ToString();
                row["Lop"] = dt2.Rows[i]["ClassName"].ToString();
                row["Khoa"] = dt2.Rows[i]["Department"].ToString();
                row["Khoas"] = dt2.Rows[i]["Courses"].ToString();
                table.Rows.Add(row);
            }
            //view=new DataView(table);
            //return view;
            return table;
        }
        //Đổ dữ liệu vào DataList
        public void LoadDataList(DataList dl, string sql)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                DataTable dt = GetDataTable(sql);
                dl.DataSource = dt;
                dl.DataBind();
                CloseConnection(cn);
            }
        }

        //Tạo List chọn trong danh sách DropDownList
        public void AndDropDownListNgay(DropDownList ddl)
        {
            for (int i = 1; i <= 31; i++)
            {
                ddl.Items.Add(Convert.ToString(i));
            }
        }

        public void AndDropDownListThang(DropDownList ddl)
        {
            for (int i = 1; i <= 12; i++)
            {
                ddl.Items.Add("Tháng " + Convert.ToString(i));
            }
        }

        public void AndDropDownListNam(DropDownList ddl)
        {
            for (int i = 2012; i <= 2013; i++)
            {
                ddl.Items.Add(Convert.ToString(i));
            }
        }

        public void AndDropDownListSo(DropDownList ddl)
        {
            for (int i = 1; i <= 10; i++)
            {
                ddl.Items.Add(Convert.ToString(i));
            }
        }

        //Tạo thư mục
        public string TaoThuMuc()
        {
            string thumuc = "D://";
            bool tontai = Directory.Exists(thumuc);
            if (tontai == false)
            {
                Directory.CreateDirectory(thumuc);
            }
            return thumuc;
        }

        //Upload file
        public void Upload(Label lab, FileUpload file, string thumuc)
        {
            lab.Text = Path.GetFileName(file.PostedFile.FileName);
            try
            {
                file.SaveAs(thumuc + "//" + lab.Text);

            }
            catch (Exception)
            {
                lab.Text = "Upload that bai";
                //throw;
            }
        }
        #region //Tài khoản sinh viên
        public void InsertAccountStudent(string sql, string pass, string radomchar, string username, string Name, string phoneNumber, string classid)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@username", SqlDbType.NChar).Value = username;
                    cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = pass;
                    cmd.Parameters.Add("@radomchar", SqlDbType.NVarChar).Value = radomchar;
                    cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Name;
                    cmd.Parameters.Add("@phone", SqlDbType.NChar).Value = phoneNumber;
                    cmd.Parameters.Add("@classid", SqlDbType.Int).Value = Convert.ToInt32(classid);
                    cmd.ExecuteNonQuery();
                }
                CloseConnection(cn);
            }
        }

        #endregion
        //Tự đông tạo mã ID
        public void GetID(string NameTable, string NameSelectColumn, TextBox txtID, string PrefixID)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                string sql = "SELECT TOP 1 " + NameSelectColumn + " FROM " + NameTable + " ORDER BY " + NameSelectColumn + " DESC";
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    string LastID;
                    int NextID;
                    if (cmd.ExecuteScalar() == null)
                    {
                        NextID = 1;
                    }
                    else
                    {
                        LastID = cmd.ExecuteScalar().ToString();
                        NextID = int.Parse(LastID.Remove(0, PrefixID.Length)) + 1;
                    }

                    string ID = "";
                    if (NextID < 10)
                    {
                        ID = PrefixID + "0000" + NextID;
                    }
                    else if (NextID < 100)
                    {
                        ID = PrefixID + "000" + NextID;
                    }
                    else if (NextID < 1000)
                    {
                        ID = PrefixID + "00" + NextID;
                    }
                    else if (NextID < 10000)
                    {
                        ID = PrefixID + "0" + NextID;
                    }
                    else ID = PrefixID + NextID;

                    txtID.Text = ID;
                }
                CloseConnection(cn);
            }
        }
    }
}